---
title: 磁盘IO
description: 磁盘I/O是如何加载数据的
---
数据库存储的基本单位是页，对于一棵 B+树的索引来说，是先从根节点找到叶子节点，也就是先查找数据行所在的页，再将页读入到内存中，在内存中对页的记录进行查找，从而得到想要的数据。

虽然想要查找的只是一条记录，但是对于磁盘 I/O 来说却需要加载一页的信息，因为页是最小的存储单位。

对于数据库来说，如果我们想要查找多行记录，查询时间是否会成倍提升呢？

其实数据库会采用缓冲池的方式提升页的查询效率。

![](https://img.wkq.pub/pic/f254372aac175d6ac571ebe9ec024777.webp)


## 数据库缓冲池
磁盘 I/O 需要消耗的时间很多，而在内存中进行操作，效率则会高很多，为了能让数据表或者索引中的数据随时被我们使用，DBMS 会申请占用内存来作为缓冲池，这样做的好处是可以让磁盘活动最小化，从而减少与磁盘直接进行 I/O 的时间。

这种策略对 SQL 语句的查询性能来说至关重要。如果索引的数据在缓冲池里，那么访问的成本就会降低很多。


缓冲池管理器会尽量将经常使用的数据保存起来，在数据库进行页面读操作的时候，首先会判断该页面是否在缓冲池中，如果存在就直接读取，如果不存在，就会通过内存或磁盘将页面存放到缓冲池在再进行读取。

缓存在数据库中的结构和作用如下图所示：

![](https://img.wkq.pub/pic/05e1692282d25768c87bbc31a1479b9b.webp)

如果我们执行 SQL 语句的时候更新了缓存池中的数据，那么这些数据会马上同步到磁盘吗？

实际上，当我们对数据库中的记录进行修改的时候，首先会修改缓冲池中页里面的记录信息，然后数据库会以一定的频率刷新到磁盘上。注意不是每次发生更新操作，都会立刻进行磁盘回写。缓冲池会采用一种叫做 checkpoint 的机制将数据回写到磁盘上。这样做的好处就是提升了数据库的整体性能。

比如，当缓冲池不够用时，需要释放掉一些不常用的页，就可以强行采用 checkpoint 的方式，将不常用的脏页回写到磁盘上，然后再从缓冲池中将这些页释放掉。这里脏页（dirty page）指的是缓冲池中被修改过的页，与磁盘上的数据不一致。

### 查看缓冲池大小

如果是 MySQL MyISAM 存储引擎，它之缓存索引，不缓存数据，对应的键缓存参数为 `key_buffer_size`。

如果使用的时 InnoDB 引擎，可以通过查看 innodb_buffer_pool_size 变量来查看缓冲池的大小，命令如下：

```sql
mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set, 1 warning (0.00 sec)
```

134217728/1024/1024=128M

```sql title='修改缓冲池大小'
mysql> set global innodb_buffer_pool_size = 134217728;
Query OK, 0 rows affected (0.00 sec)
```

在 InnoDB 存储引擎中，我们可以同时开启多个缓冲池。

```sql title='查看缓冲池的个数'
mysql> show variables like 'innodb_buffer_pool_instances';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 1     |
+------------------------------+-------+
1 row in set, 1 warning (0.00 sec)
```

你能看到当前只有一个缓冲池。实际上innodb_buffer_pool_instances默认情况下为 8，为什么只显示只有一个呢？这里需要说明的是，如果想要开启多个缓冲池，你首先需要将innodb_buffer_pool_size参数设置为大于等于 1GB，这时innodb_buffer_pool_instances才会大于 1。你可以在 MySQL 的配置文件中对innodb_buffer_pool_size进行设置，大于等于 1GB，然后再针对innodb_buffer_pool_instances参数进行修改。

## 数据页加载的三种方式

如果缓冲池中没有数据，那么缓冲池有以下三种读取数据的方式，每种方式读取的效率都是不同的：

### 1. 内存读取

如果该数据存在于内存中，基本上执行时间在 1ms 左右，效率很高。



![a5d16af8d34ebdcfe327ef7b4841ad4f](https://img.wkq.pub/pic/a5d16af8d34ebdcfe327ef7b4841ad4f.webp)

### 2. 随机读取

如果数据没有在内存里，就需要到磁盘上对该页进行查找，整体时间预估在 10ms 左右，其中有 6ms 是磁盘的实际繁忙时间（包括了寻道和半圈旋转时间），有 3ms 是对可能发生的排队时间的预估值。另外还有 1ms 的传输时间，将页从磁盘服务器缓冲区传输到数据库缓冲区。这只是一个页的读取时间。



![](https://img.wkq.pub/pic/50fb2657341103548a76fce6f7769149.webp)

### 3. 顺序读取

顺序读取其实是一种批量读取的方式，因为我们请求的数据在磁盘上往往都是相邻存储的，顺序读取可以帮我们批量读取页面，这样的话，一次性加载到缓冲池中就不需要再对其它页面单独进行 I/O 操作了。如果一个磁盘的吞吐量是 40MB/S，那么对于一个 16KB 大小的页来说，一次可以顺序读取 2560 （40MB/16KB）个页，相当于一个页的读取时间为 0.4 ms,采用批量读取，即使从磁盘上进行读取，效率也比从内存中只单独读取一个页的效率要高。

## 通过 last_query_cost 统计SQL 语句的查询成本

一条 SQL 查询语句在执行前需要确定查询计划，如果存在多种查询计划的话，MySQL 会计算每个查询计划所需要的成本，从中选择成本最小的一个作为最终执行的查询计划。

如果要想查看某条 SQL 语句的查询成本，可以在执行完这条 SQL 语句之后，通过查看当前会话中的 last_query_cost 变量值来得到当前查询的成本。这个查询成本对应的是 SQL 语句中所需要读取的页的数量。

```sql title='以 product_comment 表为例，如果我们想要查询 comment_id = 900001 的记录，然后看下查询成本'
mysql> SELECT comment_id, product_id, comment_text, user_id FROM product_comment WHERE comment_id = 900001;
+------------+------------+----------------------+---------+
| comment_id | product_id | comment_text         | user_id |
+------------+------------+----------------------+---------+
|     900001 |      10001 | 9e71ed18f49c1bb8b2dc |  595306 |
+------------+------------+----------------------+---------+
1 row in set (0.01 sec)
```

然后看下查询优化器的成本，实际上我们只需要检索一个页即可：

```sql
mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+
1 row in set (0.01 sec)
```

如果想要查询 comment_id 在 900001 到 9000100 。

```sql
mysql> SELECT comment_id, product_id, comment_text, user_id FROM product_comment WHERE comment_id BETWEEN 900001 AND 900100;
+------------+------------+----------------------+---------+
| comment_id | product_id | comment_text         | user_id |
+------------+------------+----------------------+---------+
|     900001 |      10001 | 9e71ed18f49c1bb8b2dc |  595306 |
|     900099 |      10001 | 785afb25358959e43da9 |  289569 |
|     900100 |      10001 | 9a8ed69117eb947ee7bc |  153568 |
+------------+------------+----------------------+---------+
100 rows in set (0.00 sec)
```



查询优化器的成本如下：

```sql
mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| Last_query_cost | 20.287572 |
+-----------------+-----------+
1 row in set (0.00 sec)
```

大概需要进行 20 个页的查询。

可以看到页的数量虽然是增加了，但是查询效率并没有明显的变化，实际上这俩个 SQL 查询的时间基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓存池中，然后再进行查找。虽然页数量（last_query_cost） 增加类不少，但是通过缓冲池的机制，并没有增加多少查询时间。

